﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using NHibernate;
using Spring.Collections;
using VitCode.Castle.DataAccess;

namespace VitCode.Castle.Helper
{
    public class SqlHelper : IDisposable
    {
        private readonly ParameterCache m_Cache;
        private readonly NameObjectDictionary m_ParametersAnalyzerResultCache;

        private bool m_IsSetTimeOut;
        private int m_TimeOut = 120;

        /// <summary>
        /// 
        /// </summary>
        public SqlHelper()
        {
            m_Cache = new ParameterCache();
            m_ParametersAnalyzerResultCache = new NameObjectDictionary();
        }

        /// <summary>
        /// sql执行超时时间
        /// </summary>
        public virtual int TimeOut
        {
            get { return m_TimeOut; }
            set
            {
                m_IsSetTimeOut = true;
                m_TimeOut = value;
            }
        }

        #region 执行Sql

        /// <summary>
        /// 执行一个Sql
        /// </summary>
        /// <param name="sql">需要执行的Sql</param>
        /// <param name="commandType">Sql的方式</param>
        /// <param name="session"></param>
        /// <returns></returns>
        public virtual object ExecuteScalar(ISession session, string sql, CommandType commandType)
        {
            IDbCommand comm = CteateCommand(session);
            comm.CommandText = sql;
            comm.CommandType = commandType;
            return comm.ExecuteScalar();
        }

        /// <summary>
        /// 执行一个Sql并且获取结果集的第一行第一个字段的内容
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <param name="session"></param>
        /// <returns>返回一个Sql值</returns>
        /// 
        public virtual object ExecuteScalar(ISession session, string sql)
        {
            return ExecuteScalar(session, sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一个没有结果集合的Sql
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">Sql</param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(ISession session, string sql, CommandType commandType)
        {
            IDbCommand comm = CteateCommand(session);
            comm.CommandText = sql;
            comm.CommandType = commandType;
            return comm.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行一个Sql,默认使用CommandType.Text的方式执行
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(ISession session, string sql)
        {
            return ExecuteNonQuery(session, sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一个Sql，填充一个DataSet
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql"></param>
        /// <param name="ds"></param>
        /// <exception cref="ArgumentNullException">如果Sql为空，获取ds为空</exception>
        public virtual void ExecuteDataSet(ISession session, string sql, DataSet ds)
        {
            if (sql == null)
                throw new ArgumentNullException("sql");
            if (ds == null)
                throw new ArgumentNullException("ds");
            if (sql.Length == 0)
                throw new ArgumentOutOfRangeException("sql", sql.Length, "sql为0长度");

            IDbCommand comm = CteateCommand(session);
            comm.CommandText = sql;
            IDbDataAdapter da = CreateDataAdapter(comm);
            da.Fill(ds);
        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">sql</param>
        /// <returns>返回已经填充好的DataSet</returns>
        public virtual DataSet ExecuteDataSet(ISession session, string sql)
        {
            var ds = new DataSet();
            ExecuteDataSet(session, sql, ds);
            return ds;
        }

        #endregion

        #region 可以输入带参的Sql，由数据库缓冲Sql

        /// <summary>
        /// 执行Sql,带参数，并且输入参数的顺序必须和Sql所带的参数顺序一致
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">带参数Sql</param>
        /// <param name="inputParamer">与Sql中参数顺序一致的输入参数</param>
        /// <returns></returns>
        public virtual object ExecuteScalar(ISession session, string sql, params object[] inputParamer)
        {
            IDbCommand comm = CreateCommandFromSql(session, sql, inputParamer);
            return comm.ExecuteScalar();
        }

        /// <summary>
        /// 运行Sql的
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">带参数的Sql</param>
        /// <param name="inputParams">输入参数的类型</param>
        /// <returns>返回受影响行数</returns>
        /// 
        /// <remarks></remarks>
        public virtual int ExecuteNonQuery(ISession session, string sql, params object[] inputParams)
        {
            IDbCommand comm = CreateCommandFromSql(session, sql, inputParams);
            return comm.ExecuteNonQuery();
        }

        /// <summary>
        /// 运行后获取一个DataSet
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">只能是Sql不能是存储过程</param>
        /// <param name="inputParams">输入参数的变量</param>
        /// <param name="session"></param>
        /// <returns></returns>
        public virtual DataSet ExecuteDataSet(ISession session, string sql, params object[] inputParams)
        {
            var ds = new DataSet();
            ExecuteDataSet(session, sql, ds, inputParams);
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">只能是Sql不能是存储过程</param>
        /// <param name="ds"></param>
        /// <param name="inputParams"></param>
        /// <param name="session"></param>
        public virtual void ExecuteDataSet(ISession session, string sql, DataSet ds, params object[] inputParams)
        {
            IDbCommand comm = CreateCommandFromSql(session, sql, inputParams);

            if (comm.Connection.State != ConnectionState.Open)
                comm.Connection.Open();
            IDbDataAdapter ida = CreateDataAdapter(comm);
            ida.Fill(ds);
        }


        /// <summary>
        /// 从Sql中，分析出参数，获取Command,只能是Sql,而且只能接受输入参数
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sql">Sql</param>
        /// <param name="inputParams">输入参数数值</param>
        /// <returns>返回已经完成生成Command</returns>
        /// <param name="session"></param>
        private IDbCommand CreateCommandFromSql(ISession session, string sql, params object[] inputParams)
        {
            IDbCommand comm = CteateCommand(session);
            comm.CommandText = sql;
            comm.CommandType = CommandType.Text;

            string[] paraName = GetParamers(sql);

            if (paraName.Length != inputParams.Length)
            {
                throw new ArgumentException("inputParams(" + inputParams.Length + ")和和Sql(" + paraName.Length +
                                            ")中的参数个数不一致的数目必须一致");
            }

            for (int i = 0; i < inputParams.Length; i++)
            {
                IDbDataParameter p1 = comm.CreateParameter();
                p1.Value = inputParams[i];
                p1.ParameterName = paraName[i];
                comm.Parameters.Add(p1);
            }
            return comm;
        }

        #endregion

        #region 由SqlHelper本身缓冲参数

        /// <summary>
        /// 缓冲方式执行Sql，运行过的Sql会被缓冲下来，增加执行速度
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sqlOrStoreProduce">存储过程或Sql</param>
        /// <param name="inputParamsValue">输入参数</param>
        /// <param name="paramersNames">参数名称</param>
        /// <param name="commandtype">命令执行的类型</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuery(ISession session, string sqlOrStoreProduce, object[] inputParamsValue,
                                   string[] paramersNames, CommandType commandtype)
        {
            ArrayList list;
            IDbCommand comm = CreateCacheCommand(session, sqlOrStoreProduce, inputParamsValue, paramersNames,
                                                 commandtype, out list);
            int result = comm.ExecuteNonQuery();
            FillNotInputParamterList(comm, list);
            return result;
        }

        /// <summary>
        /// 使用客户端参数缓冲方式执行Sql
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sqlOrStoreProduce"></param>
        /// <param name="inputParamsValue"></param>
        /// <param name="paramersNames"></param>
        /// <param name="commandtype"></param>
        /// <returns></returns>
        public virtual object ExecuteScalar(ISession session, string sqlOrStoreProduce, object[] inputParamsValue,
                                    string[] paramersNames, CommandType commandtype)
        {
            ArrayList list;
            IDbCommand comm = CreateCacheCommand(session, sqlOrStoreProduce, inputParamsValue, paramersNames,
                                                 commandtype, out list);
            object result = comm.ExecuteScalar();
            FillNotInputParamterList(comm, list);
            return result;
        }

        /// <summary>
        /// 创建缓冲式访问参数Command
        /// </summary>
        /// <param name="session"></param>
        /// <param name="sqlOrStoreProduce"></param>
        /// <param name="inputParamsValue"></param>
        /// <param name="paramersNames"></param>
        /// <param name="commandtype"></param>
        /// <param name="notInputParamtersList"></param>
        /// <returns></returns>
        private IDbCommand CreateCacheCommand(ISession session, string sqlOrStoreProduce, object[] inputParamsValue,
                                              string[] paramersNames, CommandType commandtype,
                                              out ArrayList notInputParamtersList)
        {
            if (inputParamsValue.Length != paramersNames.Length)
                throw new ArgumentException("inputParams和inputParamsDbType的数目必须一致");

            IDbCommand comm = CteateCommand(session);
            comm.CommandText = sqlOrStoreProduce;
            comm.CommandType = commandtype;

            notInputParamtersList = new ArrayList();

            for (int i = 0; i < inputParamsValue.Length; i++)
            {
                IDbDataParameter p1 = m_Cache[sqlOrStoreProduce, paramersNames[i]];
                if (p1 == null)
                {
                    p1 = comm.CreateParameter();
                    p1.ParameterName = paramersNames[i];

                    if (inputParamsValue[i] is NotInputParamterValue)
                    {
                        var notInputParamterValue = (NotInputParamterValue) inputParamsValue[i];
                        notInputParamterValue.Name = paramersNames[i];
                        p1.Direction = notInputParamterValue.Direction;
                        notInputParamtersList.Add(notInputParamterValue);
                    }
                    else
                        p1.Value = inputParamsValue[i];
                }
                else
                {
                    p1.Value = inputParamsValue[i];
                }

                comm.Parameters.Add(p1);
            }
            return comm;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="comm"></param>
        /// <param name="notInputParatersList"></param>
        private static void FillNotInputParamterList(IDbCommand comm, ArrayList notInputParatersList)
        {
            foreach (NotInputParamterValue v in notInputParatersList)
            {
                v.Value = ((IDbDataParameter) comm.Parameters[v.Name]).Value;
            }
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// 会清楚掉所有的缓冲，但是不会关闭连接
        /// </summary>
        public virtual void Dispose()
        {
            m_ParametersAnalyzerResultCache.Clear();
            m_Cache.Clear();
        }

        #endregion

        /// <summary>
        /// 获取DataAdapter
        /// </summary>
        /// <param name="comm">SqlCommand</param>
        /// <returns></returns>
        private static IDbDataAdapter CreateDataAdapter(IDbCommand comm)
        {
            if (comm is SqlCommand)
                return new SqlDataAdapter((SqlCommand) comm);
            if (comm is OleDbCommand)
                return new OleDbDataAdapter((OleDbCommand) comm);
            if (comm is OdbcCommand)
                return new OdbcDataAdapter((OdbcCommand) comm);
            throw new ApplicationException("暂时只能使用SqlServer和odbc");
        }

        /// <summary>
        /// 重Sql中获取参数,也就是"@的参数"字符表达式
        /// 并把他们缓冲下来
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <remarks>
        /// 以Sql作为Key，分析出的 参数缓冲到m_ParametersAnalyzerResultCache中
        /// 那么当Sql再次被运行，那么就省去分析的时间
        /// </remarks>
        private string[] GetParamers(string sql)
        {
            var result = (string[]) m_ParametersAnalyzerResultCache[sql];
            if (result == null)
            {
                result = AnalyzerParamsName(sql);
                m_ParametersAnalyzerResultCache[sql] = result;
            }
            return result;
        }

        /// <summary>
        /// 从Sql分析参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static string[] AnalyzerParamsName(string sql)
        {
            MatchCollection s = Regex.Matches(sql, "@\\w*");
            ISet resultCollection = new ListSet();
            foreach (Match m in s)
            {
                resultCollection.Add(m.Value);
            }
            var result = new string[resultCollection.Count];
            resultCollection.CopyTo(result, 0);
            return result;
        }

        /// <summary>
        /// 创建Command,如果事务不为空，
        /// </summary>
        private IDbCommand CteateCommand(ISession session)
        {
            if (!session.IsOpen)
            {
                session = session.SessionFactory.OpenSession();
            }
            if (!session.IsConnected)
            {
                session.Reconnect();
            }
            IDbCommand comm = session.Connection.CreateCommand();
            if (session.Transaction != null)
                session.Transaction.Enlist(comm);
            if (m_IsSetTimeOut)
                comm.CommandTimeout = TimeOut;
            return comm;
        }

        public DataTable GetDataTable(string sql, BaseDataAccess acc)
        {
            DataTable dt = null;
            if (!string.IsNullOrEmpty(sql) && acc!=null)
            {

                DataSet ds = ExecuteDataSet(acc.Session, sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }

            }
            return dt;
        }

        public DataTable GetDataTable(string sql, string keyProName, StringCollection queryProValues,BaseDataAccess acc)
        {
            DataTable dt = null;
            if (!string.IsNullOrEmpty(keyProName))
            {
                var nbd = new NameObjectDictionary();
                var sb = new StringBuilder(sql);
                bool notFirst = false;
                for (int i = 0; i < queryProValues.Count; i++)
                {
                    if (notFirst)
                    {
                        sb.Append(" or " + keyProName + "='");
                    }
                    else
                    {
                        sb.Append( " Where " + keyProName + "='");
                        notFirst = true;
                    }
                    sb.Append(queryProValues[i]);
                    sb.Append("'");

                    if (i % 100 == 0 || i == (queryProValues.Count - 1))
                    {
                        DataSet ds = ExecuteDataSet(acc.Session,sb.ToString());
                        if(ds != null && ds.Tables.Count > 0)
                        {
                            if (dt == null)
                                dt = ds.Tables[0].Clone();
                            foreach (DataRow r in ds.Tables[0].Rows)
                                dt.Rows.Add(r.ItemArray);
                        }
                        
                        sb = new StringBuilder(sql);
                        notFirst = false;
                    }
                }
            }
            return dt;
        }
    }
}